# -*- coding:utf-8 -*-
# 处理非正常案件
import pymysql


class DealAbnormalCase():
    def __init__(self):
        self.db2 = pymysql.connect(host="121.4.69.146", user='root', password='xhhg@2020', port=3306, db="big_data",
                                   use_unicode=True, charset='utf8')
        self.cursor2 = self.db2.cursor()

    def get_sqh_li(self):
        """
        获取非正常案件申请号
        :return: sqh_li
        """
        sqh_li = []
        with open('sqh.txt', 'r') as fr:
            for i in fr:
                sqh_li.append(i.strip())
        return sqh_li

    def get_country_state(self, sqh):
        """查询国家状态"""
        sql = f"SELECT case_status FROM xhhg_pantent_status WHERE `application_number` = 'ZL{sqh}'"
        self.cursor2.execute(sql)
        data = self.cursor2.fetchall()
        try:
            state = data[0][0]
        except:
            state = ''
        return state

    def get_fp_number(self, sqh):
        """获取发票信息"""
        sql = f"SELECT note_the_code, ticket_number FROM `big_data`.`by_payment_information` WHERE `application_number` = '{sqh}' AND `cost_category` LIKE '%实质审查费%'"
        self.cursor2.execute(sql)
        data = self.cursor2.fetchall()
        try:
            fp_num = data[0][0] + "-" + data[0][1]
        except:
            fp_num = ""
        return fp_num

    def get_ss_bz(self, sqh):
        """获取案件是否下发实审，补正通知书"""
        is_ss, is_bz = "否", "否"
        # sql = f"SELECT * FROM `big_data`.`CPCFlieUploadResult` WHERE `sqh` = '{sqh}' AND `file_type` LIKE '%进入实质审查%'"
        # self.cursor2.execute(sql)
        # data = self.cursor2.fetchall()
        # if data:
        #     is_ss = "是"
        sql = f"SELECT * FROM `big_data`.`CPCFlieUploadResult` WHERE `sqh` = '{sqh}' AND (`file_type` LIKE '%补正%' OR `file_type` LIKE '%审查意见%')"
        self.cursor2.execute(sql)
        data = self.cursor2.fetchall()
        if data:
            is_bz = "是"
        return is_bz


    def main_fun(self):
        sqh_li = self.get_sqh_li()
        for sqh in sqh_li:
            # state = self.get_country_state(sqh)
            # fp_number = self.get_fp_number(sqh)
            is_ss = self.get_ss_bz(sqh)
            print(is_ss)


if __name__ == '__main__':
    DealAbnormalCase().main_fun()
